import copy
import uuid
import xlsxwriter
from io import BytesIO
from apis import api_routes
from flask import g, request, jsonify, Response, abort
from security.decoration import certification
from sqlalchemy import and_
from typing import List
from typing import Union
from sqlalchemy import text
from sqlalchemy import func
from public.local import date_type
from urllib.parse import unquote, quote
from datetime import datetime, timedelta
from models.course.course import Course
from models.product.routes import Routes
from models.order.tasks import Tasks
from models.course.serial import Serial
from models.facility.facility import Facility


@api_routes.route("/report/serials/codes", methods=["GET"])
@certification(auth=True, role=None)
def func_serials_codes_get_api():
    """
    物料追溯模糊查询物料总码信息，最大长度256，完成API开发
    http://127.0.0.1:8080/api/report/serials/codes
    Content-Type=application/x-www-form-urlencoded
    methods=["GET"]
    {
    "codes"        string          物料总码
    }
    :return:
        {'status': 200, 'data': resp}
    ---
    tags:
      - 根据物料查询产品SN码
    """
    codes = request.args.get("codes", default=None, type=str)
    if codes is None or len(codes) == 0:
        abort(400)

    resp: list = []
    # sql_order: str = """
    #     SELECT
    #         DISTINCT(codes)
    #     FROM serials WHERE codes LIKE '%{}%' LIMIT 256 OFFSET 0
    # """
    sql_order: str = """
        SELECT 
            DISTINCT(codes)
        FROM serials WHERE codes LIKE '{}%' LIMIT 256 OFFSET 0
    """

    sql_order = sql_order.format(codes)
    data: list[tuple] = g.session.execute(text(sql_order)).fetchall()
    for itr in data:
        value, = itr
        resp.append({
            "codes": value,
            "value": value
        })
    docs: dict = {
        "codes": "后端传递值",
        "value": "总码值",
    }
    return jsonify({'status': 200, 'data': resp, "docs": docs})


@api_routes.route("/report/serials/serial", methods=["GET"])
@certification(auth=True, role=None)
def func_serials_serial_get_api():
    """
    物料追溯模糊查询物料子码信息，最大长度256，完成API开发
    http://127.0.0.1:8080/api/report/serials/serial
    Content-Type=application/x-www-form-urlencoded
    methods=["GET"]
    {
    "serial"        string          物料子码
    }
    :return:
        {'status': 200, 'data': resp}
    ---
    tags:
      - 根据物料查询产品SN码
    """
    serial = request.args.get("serial", default=None, type=str)
    if serial is None or len(serial) == 0:
        abort(400)

    sql_order: str = """
        SELECT 
            DISTINCT(serial)
        FROM serials WHERE serial LIKE '{}%' LIMIT 256 OFFSET 0
    """

    resp: list = []
    sql_order = sql_order.format(serial)
    data: list[tuple] = g.session.execute(text(sql_order)).fetchall()

    for itr in data:
        resp.append({
            "serial": itr[0],
            "value": itr[0]
        })

    docs: dict = {
        "codes": "后端传递值",
        "value": "子码值",
    }
    return jsonify({'status': 200, 'data': resp, "docs": docs})


@api_routes.route("/report/serials", methods=["GET"])
@certification(auth=True, role=None)
def func_serials_get_api():
    """
    物料追溯查询信息（物料总码、物料子码、产品SN、日期可以自由组合），完成API开发
    这个表数据量第二多，查询最复杂，如果不指定日期，查询相对比较慢，前端最好做个默认日期（前面60天到今天）
    http://127.0.0.1:8080/api/report/serials
    Content-Type=application/x-www-form-urlencoded
    methods=["GET"]
    {
    "start"        string          开始时间, 格式 %Y-%m-%d
    "finish"       string          结束时间  格式 %Y-%m-%d
    "index"         int      ****  第几页，默认为1
    "size"          int      ****  每页数据多少, 默认每页10条数据
    "codes"        string          物料总码
    "serial"       string          物料子码
    "sequence"     string          产品SN, 不支持模糊查询
    }
    :return:
        {'status': 200, 'data': resp}
    ---
    tags:
      - 根据物料查询产品SN码
    """

    start: Union[str, None] = request.args.get("start", default=None, type=str)
    finish: Union[str, None] = request.args.get("finish", default=None, type=str)
    size = request.args.get("size", default=10, type=int)
    index = request.args.get("index", default=1, type=int)
    codes = request.args.get("codes", default=None, type=str)
    serial = request.args.get("serial", default=None, type=str)
    sequence: Union[str, None] = request.args.get("sequence", default=None, type=str)

    if serial is not None and len(serial) == 0:
        serial = None

    if codes is not None and len(codes) == 0:
        codes = None

    if start is not None and len(start) != 0 and finish is not None and len(finish) != 0:
        arg1 = datetime.strptime(finish, "%Y-%m-%d") + timedelta(days=1) - timedelta(seconds=1)
        arg0 = datetime.strptime(start, "%Y-%m-%d")
        start: str = arg0.strftime("%Y-%m-%d %H:%M:%S")
        finish: str = arg1.strftime("%Y-%m-%d %H:%M:%S")
    else:
        start = None
        finish = None

    if sequence is not None and len(sequence) == 0:
        sequence = None

    sql_count: Union[str, None] = None
    sql_data: Union[str, None] = None

    if codes is not None and serial is None and sequence is None:
        sql_count = """
            SELECT 
                COUNT(sequences) FROM (
                    SELECT 
                        serials.sequence AS sequences
                    FROM serials INNER JOIN courses ON serials.course_id = courses.id 
                    WHERE courses.mark >= 0 AND serials.codes = :codes {} GROUP BY serials.sequence
                ) 
            AS temp
        """
        sql_data = """
            SELECT 
                courses.id, courses.sequence,
                courses.mark, courses.message,
                tasks.orders, tasks.name, aircraft.aircraft,
                COALESCE(TO_CHAR(courses.start,'yyyy-MM-dd HH24:MI:SS'), NULL) as start,
                COALESCE(TO_CHAR(courses.finish,'yyyy-MM-dd HH24:MI:SS'), NULL) as finish
            FROM courses 
            INNER JOIN (
                SELECT 
                    MIN(courses.id) AS course_id, MIN(serials.id) AS serial_id, serials.sequence
                FROM serials INNER JOIN courses ON serials.course_id = courses.id
                WHERE courses.mark >= 0 AND serials.codes = :codes {} 
                GROUP BY serials.sequence ORDER BY serial_id LIMIT :arg3 OFFSET :arg4
            )AS temps ON temps.sequence = courses.sequence
            INNER JOIN tasks ON courses.tasks_id = tasks.id 
            INNER JOIN aircraft ON courses.aircraft_id = aircraft.id
            ORDER BY courses.id
        """
    elif codes is None and serial is not None and sequence is None:
        sql_count = """
            SELECT 
                COUNT(sequences) FROM (
                    SELECT 
                        serials.sequence AS sequences
                    FROM serials INNER JOIN courses ON serials.course_id = courses.id 
                    WHERE courses.mark >= 0 AND serials.serial = :serial {} GROUP BY serials.sequence
                ) 
            AS temp
        """
        sql_data = """
            SELECT 
                courses.id, courses.sequence,
                courses.mark, courses.message,
                tasks.orders, tasks.name, aircraft.aircraft,
                COALESCE(TO_CHAR(courses.start,'yyyy-MM-dd HH24:MI:SS'), NULL) as start,
                COALESCE(TO_CHAR(courses.finish,'yyyy-MM-dd HH24:MI:SS'), NULL) as finish
            FROM courses 
            INNER JOIN (
                SELECT 
                    MIN(courses.id) AS course_id, MIN(serials.id) AS serial_id, serials.sequence
                FROM serials INNER JOIN courses ON serials.course_id = courses.id
                WHERE courses.mark >= 0 AND serials.serial = :serial {} 
                GROUP BY serials.sequence ORDER BY serial_id LIMIT :arg3 OFFSET :arg4
            )AS temps ON temps.sequence = courses.sequence
            INNER JOIN tasks ON courses.tasks_id = tasks.id 
            INNER JOIN aircraft ON courses.aircraft_id = aircraft.id
            ORDER BY courses.id
        """
    elif codes is None and serial is None and sequence is not None:
        sql_count = """
            SELECT 
                COUNT(sequences) FROM (
                    SELECT 
                        serials.sequence AS sequences
                    FROM serials INNER JOIN courses ON serials.course_id = courses.id 
                    WHERE courses.mark >= 0 AND serials.sequence = :seq {} GROUP BY serials.sequence
                ) 
            AS temp
        """
        sql_data = """
            SELECT 
                courses.id, courses.sequence,
                courses.mark, courses.message,
                tasks.orders, tasks.name, aircraft.aircraft,
                COALESCE(TO_CHAR(courses.start,'yyyy-MM-dd HH24:MI:SS'), NULL) as start,
                COALESCE(TO_CHAR(courses.finish,'yyyy-MM-dd HH24:MI:SS'), NULL) as finish
            FROM courses 
            INNER JOIN (
                SELECT 
                    MIN(courses.id) AS course_id, MIN(serials.id) AS serial_id, serials.sequence
                FROM serials INNER JOIN courses ON serials.course_id = courses.id
                WHERE courses.mark >= 0 AND serials.sequence = :seq {} 
                GROUP BY serials.sequence ORDER BY serial_id LIMIT :arg3 OFFSET :arg4
            )AS temps ON temps.sequence = courses.sequence
            INNER JOIN tasks ON courses.tasks_id = tasks.id 
            INNER JOIN aircraft ON courses.aircraft_id = aircraft.id
            ORDER BY courses.id
        """
    elif codes is not None and serial is not None and sequence is None:
        sql_count = """
            SELECT 
                COUNT(sequences) FROM (
                    SELECT 
                        serials.sequence AS sequences
                    FROM serials INNER JOIN courses ON serials.course_id = courses.id 
                    WHERE courses.mark >= 0 AND serials.codes = :codes AND serials.serial = :serial {} 
                    GROUP BY serials.sequence
                ) 
            AS temp
        """
        sql_data = """
            SELECT 
                courses.id, courses.sequence,
                courses.mark, courses.message,
                tasks.orders, tasks.name, aircraft.aircraft,
                COALESCE(TO_CHAR(courses.start,'yyyy-MM-dd HH24:MI:SS'), NULL) as start,
                COALESCE(TO_CHAR(courses.finish,'yyyy-MM-dd HH24:MI:SS'), NULL) as finish
            FROM courses 
            INNER JOIN (
                SELECT 
                    MIN(courses.id) AS course_id, MIN(serials.id) AS serial_id, serials.sequence
                FROM serials INNER JOIN courses ON serials.course_id = courses.id
                WHERE courses.mark >= 0 AND serials.codes = :codes AND serials.serial = :serial {} 
                GROUP BY serials.sequence ORDER BY serial_id LIMIT :arg3 OFFSET :arg4
            )AS temps ON temps.sequence = courses.sequence
            INNER JOIN tasks ON courses.tasks_id = tasks.id 
            INNER JOIN aircraft ON courses.aircraft_id = aircraft.id
            ORDER BY courses.id
        """
    elif codes is not None and serial is None and sequence is not None:
        sql_count = """
            SELECT 
                COUNT(sequences) FROM (
                    SELECT 
                        serials.sequence AS sequences
                    FROM serials INNER JOIN courses ON serials.course_id = courses.id 
                    WHERE courses.mark >= 0 AND serials.codes = :codes AND serials.sequence = :seq {} 
                    GROUP BY serials.sequence
                ) 
            AS temp
        """
        sql_data = """
            SELECT 
                courses.id, courses.sequence,
                courses.mark, courses.message,
                tasks.orders, tasks.name, aircraft.aircraft,
                COALESCE(TO_CHAR(courses.start,'yyyy-MM-dd HH24:MI:SS'), NULL) as start,
                COALESCE(TO_CHAR(courses.finish,'yyyy-MM-dd HH24:MI:SS'), NULL) as finish
            FROM courses 
            INNER JOIN (
                SELECT 
                    MIN(courses.id) AS course_id, MIN(serials.id) AS serial_id, serials.sequence
                FROM serials INNER JOIN courses ON serials.course_id = courses.id
                WHERE courses.mark >= 0 AND serials.codes = :codes AND serials.sequence = :seq {} 
                GROUP BY serials.sequence ORDER BY serial_id LIMIT :arg3 OFFSET :arg4
            )AS temps ON temps.sequence = courses.sequence
            INNER JOIN tasks ON courses.tasks_id = tasks.id 
            INNER JOIN aircraft ON courses.aircraft_id = aircraft.id
            ORDER BY courses.id
        """
    elif codes is None and serial is not None and sequence is not None:
        sql_count = """
            SELECT 
                COUNT(sequences) FROM (
                    SELECT 
                        serials.sequence AS sequences
                    FROM serials INNER JOIN courses ON serials.course_id = courses.id 
                    WHERE courses.mark >= 0 AND serials.serial = :serial AND serials.sequence = :seq {} 
                    GROUP BY serials.sequence
                ) 
            AS temp
        """
        sql_data = """
            SELECT 
                courses.id, courses.sequence,
                courses.mark, courses.message,
                tasks.orders, tasks.name, aircraft.aircraft,
                COALESCE(TO_CHAR(courses.start,'yyyy-MM-dd HH24:MI:SS'), NULL) as start,
                COALESCE(TO_CHAR(courses.finish,'yyyy-MM-dd HH24:MI:SS'), NULL) as finish
            FROM courses 
            INNER JOIN (
                SELECT 
                    MIN(courses.id) AS course_id, MIN(serials.id) AS serial_id, serials.sequence
                FROM serials INNER JOIN courses ON serials.course_id = courses.id
                WHERE courses.mark >= 0 AND serials.serial = :serial AND serials.sequence = :seq {} 
                GROUP BY serials.sequence ORDER BY serial_id LIMIT :arg3 OFFSET :arg4
            )AS temps ON temps.sequence = courses.sequence
            INNER JOIN tasks ON courses.tasks_id = tasks.id 
            INNER JOIN aircraft ON courses.aircraft_id = aircraft.id
            ORDER BY courses.id
        """
    elif codes is not None and serial is not None and sequence is not None:
        sql_count = """
            SELECT 
                COUNT(sequences) FROM (
                    SELECT 
                        serials.sequence AS sequences
                    FROM serials INNER JOIN courses ON serials.course_id = courses.id 
                    WHERE courses.mark >= 0 AND serials.codes = :codes 
                    AND serials.serial = :serial AND serials.sequence = :seq {} 
                    GROUP BY serials.sequence
                ) 
            AS temp
        """
        sql_data = """
            SELECT 
                courses.id, courses.sequence,
                courses.mark, courses.message,
                tasks.orders, tasks.name, aircraft.aircraft,
                COALESCE(TO_CHAR(courses.start,'yyyy-MM-dd HH24:MI:SS'), NULL) as start,
                COALESCE(TO_CHAR(courses.finish,'yyyy-MM-dd HH24:MI:SS'), NULL) as finish
            FROM courses 
            INNER JOIN (
                SELECT 
                    MIN(courses.id) AS course_id, MIN(serials.id) AS serial_id, serials.sequence
                FROM serials INNER JOIN courses ON serials.course_id = courses.id
                WHERE courses.mark >= 0 AND serials.codes = :codes 
                AND serials.serial = :serial AND serials.sequence = :seq {} 
                GROUP BY serials.sequence ORDER BY serial_id LIMIT :arg3 OFFSET :arg4
            )AS temps ON temps.sequence = courses.sequence
            INNER JOIN tasks ON courses.tasks_id = tasks.id 
            INNER JOIN aircraft ON courses.aircraft_id = aircraft.id
            ORDER BY courses.id
        """
    else:
        sql_count = """
            SELECT 
                COUNT(sequences) FROM (
                    SELECT 
                        serials.sequence AS sequences
                    FROM serials INNER JOIN courses ON serials.course_id = courses.id 
                    WHERE courses.mark >= 0 {} 
                    GROUP BY serials.sequence
                ) 
            AS temp
        """
        sql_data = """
            SELECT 
                courses.id, courses.sequence,
                courses.mark, courses.message,
                tasks.orders, tasks.name, aircraft.aircraft,
                COALESCE(TO_CHAR(courses.start,'yyyy-MM-dd HH24:MI:SS'), NULL) as start,
                COALESCE(TO_CHAR(courses.finish,'yyyy-MM-dd HH24:MI:SS'), NULL) as finish
            FROM courses 
            INNER JOIN (
                SELECT 
                    MIN(courses.id) AS course_id, MIN(serials.id) AS serial_id, serials.sequence
                FROM serials INNER JOIN courses ON serials.course_id = courses.id WHERE courses.mark >= 0 {} 
                GROUP BY serials.sequence ORDER BY serial_id LIMIT :arg3 OFFSET :arg4
            )AS temps ON temps.sequence = courses.sequence
            INNER JOIN tasks ON courses.tasks_id = tasks.id 
            INNER JOIN aircraft ON courses.aircraft_id = aircraft.id
            ORDER BY courses.id
        """

    if start is not None and finish is not None:
        sql_count = sql_count.format("AND serials.time >= :arg0 AND serials.time <= :arg1")
        sql_data = sql_data.format("AND serials.time >= :arg0 AND serials.time <= :arg1")
    else:
        sql_count = sql_count.format("")
        sql_data = sql_data.format("")

    parm: dict = {
        "arg0": start, "arg1": finish, "codes": codes,
        "serial": serial, "seq": sequence, "arg3": size, "arg4": (index - 1) * size
    }
    count: int = g.session.execute(text(sql_count), parm).fetchone()[0]
    date: list = g.session.execute(text(sql_data), parm).fetchall()
    rsp, page = [], int(count / size) if count / size == int(count / size) else int(count / size) + 1

    parm2: dict = {0: "未完成", 1: "合格", 2: "不合格", 3: "报废"}
    for index, itr in enumerate(date):
        db_serials: List[Serial] = g.session.query(Serial).filter(Serial.sequence == itr[1]).order_by(Serial.id).all()
        serials: list = []
        for index2, itr2 in enumerate(db_serials):
            facility: Union[Facility, None] = g.session.query(Facility).filter(Facility.id == itr2.facility_id).first()
            serials.append({
                "name": itr2.name,
                "facility_name": facility.name if facility else None,
                "codes": itr2.codes,
                "serial": itr2.serial
            })
        rsp.append({
            "course_id": itr[0],
            "sequence": itr[1],
            "mark": parm2.get(itr.mark),
            "message": itr[3],
            "orders": itr[4],
            "name": itr[5],
            "aircraft": itr[6],
            "start": itr[7],
            "finish": itr[8],
            "serials": copy.deepcopy(serials)
        })

    doc: dict = {
        "course_id": "对应数据库ID",
        "sequence": "产品SN码",
        "mark": "标记",
        "message": "产品报废原因说明",
        "orders": "订单编号",
        "name": "工单名称",
        "aircraft": "机种名称",
        "start": "加工时间",
        "finish": "加工结束时间",
        "serials": "物料"
    }
    return jsonify({'status': 200, 'data': rsp, "index": index, "size": size, "total": count, "page": page, "doc": doc})


@api_routes.route("/report/serials/output", methods=["GET"])
# @certification(auth=True, role=None)
def func_serials_output_get_api():
    """
    物料追溯查询信息导出EXCEL，最多一次导出16W条数据，完成API开发
    http://127.0.0.1:8080/api/report/serials/output
    Content-Type=application/x-www-form-urlencoded
    methods=["GET"]
    {
    "start"        string          开始时间  格式 %Y-%m-%d
    "finish"       string          结束时间  格式 %Y-%m-%d
    "codes"        string          物料总码
    "serial"       string          物料子码
    "sequence"     string          产品SN
    "file"         string          导出文件名字，默认随机生产uuid4
    }
    :return:
        {'status': 200, 'data': resp}
    ---
    tags:
      - 根据物料查询产品SN码
    """
    start: Union[str, None] = request.args.get("start", default=None, type=str)
    finish: Union[str, None] = request.args.get("finish", default=None, type=str)
    codes: Union[str, None] = request.args.get("codes", default=None, type=str)
    serial: Union[str, None] = request.args.get("serial", default=None, type=str)
    sequence: Union[str, None] = request.args.get("sequence", default=None, type=str)
    file: Union[str, None] = request.args.get("file", default=None, type=str)

    file_name = "{}.xlsx".format(uuid.uuid4().hex)
    if file is not None:
        file_name = "{}.xlsx".format(file)

    if finish is not None and len(finish) != 0 and start is not None and len(start) > 0:
        arg0 = datetime.strptime(start, "%Y-%m-%d")
        arg1 = datetime.strptime(finish, "%Y-%m-%d") + timedelta(days=1) - timedelta(seconds=1)
        finish: str = arg1.strftime("%Y-%m-%d %H:%M:%S")
        start: str = arg0.strftime("%Y-%m-%d %H:%M:%S")
    else:
        start = None
        finish = None

    if sequence is not None and len(sequence) == 0:
        sequence = None

    sql_data: Union[str, None] = None
    if codes is not None and serial is None and sequence is None:
        sql_data = """
            SELECT 
                courses.id, courses.sequence,
                courses.mark, courses.message,
                tasks.orders, tasks.name, aircraft.aircraft,
                COALESCE(TO_CHAR(courses.start,'yyyy-MM-dd HH24:MI:SS'), NULL) as start,
                COALESCE(TO_CHAR(courses.finish,'yyyy-MM-dd HH24:MI:SS'), NULL) as finish
            FROM courses 
            INNER JOIN (
                SELECT 
                    MIN(courses.id) AS course_id, MIN(serials.id) AS serial_id, serials.sequence
                FROM serials INNER JOIN courses ON serials.course_id = courses.id
                WHERE courses.mark >= 0 AND serials.codes = :codes {} 
                GROUP BY serials.sequence ORDER BY serial_id LIMIT :arg3 OFFSET :arg4
            )AS temps ON temps.sequence = courses.sequence
            INNER JOIN tasks ON courses.tasks_id = tasks.id 
            INNER JOIN aircraft ON courses.aircraft_id = aircraft.id
            ORDER BY courses.id
        """
    elif codes is None and serial is not None and sequence is None:
        sql_data = """
            SELECT 
                courses.id, courses.sequence,
                courses.mark, courses.message,
                tasks.orders, tasks.name, aircraft.aircraft,
                COALESCE(TO_CHAR(courses.start,'yyyy-MM-dd HH24:MI:SS'), NULL) as start,
                COALESCE(TO_CHAR(courses.finish,'yyyy-MM-dd HH24:MI:SS'), NULL) as finish
            FROM courses 
            INNER JOIN (
                SELECT 
                    MIN(courses.id) AS course_id, MIN(serials.id) AS serial_id, serials.sequence
                FROM serials INNER JOIN courses ON serials.course_id = courses.id
                WHERE courses.mark >= 0 AND serials.serial = :serial {} 
                GROUP BY serials.sequence ORDER BY serial_id LIMIT :arg3 OFFSET :arg4
            )AS temps ON temps.sequence = courses.sequence
            INNER JOIN tasks ON courses.tasks_id = tasks.id 
            INNER JOIN aircraft ON courses.aircraft_id = aircraft.id
            ORDER BY courses.id
        """
    elif codes is None and serial is None and sequence is not None:
        sql_data = """
            SELECT 
                courses.id, courses.sequence,
                courses.mark, courses.message,
                tasks.orders, tasks.name, aircraft.aircraft,
                COALESCE(TO_CHAR(courses.start,'yyyy-MM-dd HH24:MI:SS'), NULL) as start,
                COALESCE(TO_CHAR(courses.finish,'yyyy-MM-dd HH24:MI:SS'), NULL) as finish
            FROM courses 
            INNER JOIN (
                SELECT 
                    MIN(courses.id) AS course_id, MIN(serials.id) AS serial_id, serials.sequence
                FROM serials INNER JOIN courses ON serials.course_id = courses.id
                WHERE courses.mark >= 0 AND serials.sequence = :seq {} 
                GROUP BY serials.sequence ORDER BY serial_id LIMIT :arg3 OFFSET :arg4
            )AS temps ON temps.sequence = courses.sequence
            INNER JOIN tasks ON courses.tasks_id = tasks.id 
            INNER JOIN aircraft ON courses.aircraft_id = aircraft.id
            ORDER BY courses.id
        """
    elif codes is not None and serial is not None and sequence is None:
        sql_data = """
            SELECT 
                courses.id, courses.sequence,
                courses.mark, courses.message,
                tasks.orders, tasks.name, aircraft.aircraft,
                COALESCE(TO_CHAR(courses.start,'yyyy-MM-dd HH24:MI:SS'), NULL) as start,
                COALESCE(TO_CHAR(courses.finish,'yyyy-MM-dd HH24:MI:SS'), NULL) as finish
            FROM courses 
            INNER JOIN (
                SELECT 
                    MIN(courses.id) AS course_id, MIN(serials.id) AS serial_id, serials.sequence
                FROM serials INNER JOIN courses ON serials.course_id = courses.id
                WHERE courses.mark >= 0 AND serials.codes = :codes AND serials.serial = :serial {} 
                GROUP BY serials.sequence ORDER BY serial_id LIMIT :arg3 OFFSET :arg4
            )AS temps ON temps.sequence = courses.sequence
            INNER JOIN tasks ON courses.tasks_id = tasks.id 
            INNER JOIN aircraft ON courses.aircraft_id = aircraft.id
            ORDER BY courses.id
        """
    elif codes is not None and serial is None and sequence is not None:
        sql_data = """
            SELECT 
                courses.id, courses.sequence,
                courses.mark, courses.message,
                tasks.orders, tasks.name, aircraft.aircraft,
                COALESCE(TO_CHAR(courses.start,'yyyy-MM-dd HH24:MI:SS'), NULL) as start,
                COALESCE(TO_CHAR(courses.finish,'yyyy-MM-dd HH24:MI:SS'), NULL) as finish
            FROM courses 
            INNER JOIN (
                SELECT 
                    MIN(courses.id) AS course_id, MIN(serials.id) AS serial_id, serials.sequence
                FROM serials INNER JOIN courses ON serials.course_id = courses.id
                WHERE courses.mark >= 0 AND serials.codes = :codes AND serials.sequence = :seq {} 
                GROUP BY serials.sequence ORDER BY serial_id LIMIT :arg3 OFFSET :arg4
            )AS temps ON temps.sequence = courses.sequence
            INNER JOIN tasks ON courses.tasks_id = tasks.id 
            INNER JOIN aircraft ON courses.aircraft_id = aircraft.id
            ORDER BY courses.id
        """
    elif codes is None and serial is not None and sequence is not None:
        sql_data = """
            SELECT 
                courses.id, courses.sequence,
                courses.mark, courses.message,
                tasks.orders, tasks.name, aircraft.aircraft,
                COALESCE(TO_CHAR(courses.start,'yyyy-MM-dd HH24:MI:SS'), NULL) as start,
                COALESCE(TO_CHAR(courses.finish,'yyyy-MM-dd HH24:MI:SS'), NULL) as finish
            FROM courses 
            INNER JOIN (
                SELECT 
                    MIN(courses.id) AS course_id, MIN(serials.id) AS serial_id, serials.sequence
                FROM serials INNER JOIN courses ON serials.course_id = courses.id
                WHERE courses.mark >= 0 AND serials.serial = :serial AND serials.sequence = :seq {} 
                GROUP BY serials.sequence ORDER BY serial_id LIMIT :arg3 OFFSET :arg4
            )AS temps ON temps.sequence = courses.sequence
            INNER JOIN tasks ON courses.tasks_id = tasks.id 
            INNER JOIN aircraft ON courses.aircraft_id = aircraft.id
            ORDER BY courses.id
        """
    elif codes is not None and serial is not None and sequence is not None:
        sql_data = """
            SELECT 
                courses.id, courses.sequence,
                courses.mark, courses.message,
                tasks.orders, tasks.name, aircraft.aircraft,
                COALESCE(TO_CHAR(courses.start,'yyyy-MM-dd HH24:MI:SS'), NULL) as start,
                COALESCE(TO_CHAR(courses.finish,'yyyy-MM-dd HH24:MI:SS'), NULL) as finish
            FROM courses 
            INNER JOIN (
                SELECT 
                    MIN(courses.id) AS course_id, MIN(serials.id) AS serial_id, serials.sequence
                FROM serials INNER JOIN courses ON serials.course_id = courses.id
                WHERE courses.mark >= 0 AND serials.codes = :codes 
                AND serials.serial = :serial AND serials.sequence = :seq {} 
                GROUP BY serials.sequence ORDER BY serial_id LIMIT :arg3 OFFSET :arg4
            )AS temps ON temps.sequence = courses.sequence
            INNER JOIN tasks ON courses.tasks_id = tasks.id 
            INNER JOIN aircraft ON courses.aircraft_id = aircraft.id
            ORDER BY courses.id
        """
    else:
        sql_data = """
            SELECT 
                courses.id, courses.sequence,
                courses.mark, courses.message,
                tasks.orders, tasks.name, aircraft.aircraft,
                COALESCE(TO_CHAR(courses.start,'yyyy-MM-dd HH24:MI:SS'), NULL) as start,
                COALESCE(TO_CHAR(courses.finish,'yyyy-MM-dd HH24:MI:SS'), NULL) as finish
            FROM courses 
            INNER JOIN (
                SELECT 
                    MIN(courses.id) AS course_id, MIN(serials.id) AS serial_id, serials.sequence
                FROM serials INNER JOIN courses ON serials.course_id = courses.id WHERE courses.mark >= 0 {} 
                GROUP BY serials.sequence ORDER BY serial_id LIMIT :arg3 OFFSET :arg4
            )AS temps ON temps.sequence = courses.sequence
            INNER JOIN tasks ON courses.tasks_id = tasks.id 
            INNER JOIN aircraft ON courses.aircraft_id = aircraft.id
            ORDER BY courses.id
        """

    if start is not None and finish is not None:
        sql_data = sql_data.format("AND serials.time >= :arg0 AND serials.time <= :arg1")
    else:
        sql_data = sql_data.format("")

    parm: dict = {
        "arg0": start, "arg1": finish, "codes": codes,
        "serial": serial, "seq": sequence, "arg3": 160000, "arg4": 0
    }
    data: list = g.session.execute(text(sql_data), parm).fetchall()

    output = BytesIO()
    workbook = xlsxwriter.Workbook(output, {'in_memory': True})

    worksheet_context = workbook.add_worksheet("物料生产产品清单")
    parm2: dict = {0: "未完成", 1: "合格", 2: "不合格", 3: "报废"}
    context_title = ["产品SN码", "是否合格", "产品报废原因说明", "订单编号", "工单名称", "机种名称", "加工时间", "加工结束时间"]
    worksheet_context.write_row(row=0, col=0, data=context_title)
    for index, itr in enumerate(data):
        sequences = index + 1
        temp = [itr[1], parm2.get(itr.mark), itr[3], itr[4], itr[5], itr[6], itr[7], itr[8]]

        db_serials: List[Serial] = g.session.query(Serial).filter(Serial.sequence == itr[1]).order_by(Serial.id).all()
        for itr2 in db_serials:
            temp.append(str({
                "名称": itr2.name,
                "总码": itr2.codes,
                "子码": itr2.serial
            }))

        worksheet_context.write_row(row=sequences, col=0, data=temp)

    workbook.close()
    output.seek(0)
    resp = Response(output, content_type='application/octet-stream')
    resp.headers["Content-Disposition"] = "attachment; filename={0}; filename*=utf-8''{0}".format(quote(file_name))
    resp.headers.set("Access-Control-Expose-Headers", "*")
    return resp, 200
